﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Linq.Expressions;
using System.Text;
using System.Collections.Concurrent;
using System.Threading.Tasks;
using System.Linq;
using CNative.Utilities;

namespace CNative.DbUtils
{
    internal abstract class BaseSqlBuilder
    {
        #region var
        protected readonly IDbHelper db = null;
        protected readonly SqlEntity sqlent = null;
        protected readonly List<SqlEntity> sqlList = null;
        /// <summary>
        /// 防止重复生成脚本
        /// </summary>
        protected bool isBuilded = false;

        /// <summary>
        /// 参数关键字 @ or :
        /// </summary>
        protected readonly string paramKeyword = "";
        /// <summary>
        /// 关键字前缀 [
        /// </summary>
        protected readonly string suffixLeft = "";
        /// <summary>
        /// 关键字后缀 ]
        /// </summary>
        protected readonly string suffixRigh = "";
        /// <summary>
        /// 获取数据库时间函数
        /// </summary>
        protected readonly string Fun_GetDate = "";

        protected Tuple<DbTableInfo, string, string> tupleTableInfo = null;
        protected readonly HashSet<string> FieldList = new HashSet<string>();
        protected readonly HashSet<string> WhereList = new HashSet<string>();
        protected string fromStr = "";
        protected int _Top = -1;
        #endregion

        #region BaseSqlBuilder
        public BaseSqlBuilder(IDbHelper _db)
        {
            db = _db;
            sqlent = CreateSqlEntity();
            sqlList = new List<SqlEntity>();

            suffixLeft = db.SqlDbProvider.SuffixLeft;
            suffixRigh = db.SqlDbProvider.SuffixRigh;
            paramKeyword = db.SqlDbProvider.ParamKeyword;

            Fun_GetDate = db.SqlDbProvider.SqlDateNow;
        }
        #endregion

        #region GetTableInfo  
        /// <summary>
        /// 获取实体对应表信息
        /// </summary>
        /// <typeparam name="TClass"></typeparam>
        /// <param name="tableAlias">表别名</param>
        /// <returns>
        /// <para>
        /// DbTableInfo
        /// </para>
        /// <para>
        /// TableAlias
        /// </para>
        /// <para>
        /// SchemaTableName
        /// </para>
        ///</returns>
        protected virtual Tuple<DbTableInfo, string, string> GetTableInfo<TClass>(string tableAlias = "") where TClass : class, new()
        {
            return GetTableInfo(typeof(TClass), tableAlias);
        }
        protected virtual Tuple<DbTableInfo, string, string> GetTableInfo(Type classType, string tableAlias = "")
        {
            var tb = Funs.GetDbTableInfo(db, classType);
            return new Tuple<DbTableInfo, string, string>(
               tb
               , SqlAliasHelper.GetTableAlias(classType, tableAlias)
               , Funs.GetSchemaTableName(db, classType));
        }
        //-------------------------------------------------------------------------------------------------------------------------------
        /// <summary>
        /// 开始
        /// </summary>
        /// <typeparam name="TClass"></typeparam>
        /// <returns></returns>
        protected virtual Tuple<DbTableInfo, string, string> From<TClass>(string tableAlias = "") where TClass : class, new()
        {
            tupleTableInfo = GetTableInfo<TClass>(tableAlias?.Replace(".", ""));
            if (fromStr.IsNullOrEmpty())
            {
                fromStr = $"{tupleTableInfo.Item3} {tupleTableInfo.Item2.Replace(".", "")}";
            }
            return tupleTableInfo;
        }
        #endregion

        #region Build
        /// <summary>
        /// 生成脚本
        /// </summary>
        /// <param name="isCount"></param>
        /// <returns></returns>
        protected virtual bool Build(bool isCount = false)
        {
            fromStr.ArgumentNullException("fromStr is null,未执行 From<TClass>() ");

            //if (sqlent == null) sqlent = new SqlEntity(db.DBType);
            sqlent.Sql = "";
            return true;
        }
        #endregion

        #region Dispose 
        public virtual void Dispose()
        {
            SqlAliasHelper.ClearAliases();
            isBuilded = false;
            FieldList?.Clear();
            WhereList?.Clear();
            fromStr = "";

            sqlList.Clear();
            if (sqlent.Parameters == null) sqlent.Parameters = new List<IDataParameter>();
            sqlent.Sql = "";
            sqlent.Parameters.Clear();
        }
        #endregion
        //-------------------------------------------------------------------------------------------------------------------------------
        #region select
        /// <summary>
        /// 查寻返回DataSet
        /// </summary>
        /// <returns></returns>
        public DataSet QueryDataSet()
        {
            Build();
            var ret = db.QueryDataSet(sqlent);
            Dispose();
            return ret;
        }
        /// <summary>
        /// 查寻返回DataTable
        /// </summary>
        /// <returns></returns>
        public DataTable QueryDataTable()
        {
            Build();
            var ret = db.QueryDataTable(sqlent);
            Dispose();
            return ret;
        }
        /// <summary>
        /// 查寻返回实体集合
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <returns></returns>
        public List<T> Query<T>() where T : class
        {
            Build();
            var ret = db.Query<T>(sqlent);
            Dispose();
            return ret;
        }
        /// <summary>
        /// 查寻返回单个字段值
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <returns></returns>
        public T GetSingle<T>()
        {
            _Top = 1;
            Build();
            var ret = db.GetSingle<T>(sqlent);
            Dispose();
            return ret;
        }
        /// <summary>
        /// 查寻返回单行实体
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <returns></returns>
        public T GetSingleRow<T>() where T : class
        {
            _Top = 1;
            Build();

            var ret = db.GetSingleRow<T>(sqlent);
            Dispose();
            return ret;
        }
        /// <summary>
        /// 返回数据库当前时间
        /// </summary>
        /// <returns></returns>
        public DateTime GetDateNow()
        {
            try
            {
                sqlent.Sql = db.SqlDbProvider.FullSqlDateNow;
                var ret = db.GetSingle<string>(sqlent);
                Dispose();
                return ret.ToDateTime();

            }
            catch (Exception ex)
            {
            }
            return DateTime.Now;
        }
        //---------------------------------------------------------------------------------
#if !NET40
        /// <summary>
        /// 异步返回DataSet
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public Task<DataSet> QueryDataSetAsync()
        {
            return Task.FromResult(QueryDataSet());
        }
        /// <summary>
        /// 异步返回DataTable
        /// </summary>
        /// <returns></returns>
        public Task<DataTable> QueryDataTableAsync()
        {
            return Task.FromResult(QueryDataTable());
        }
        /// <summary>
        /// 异步返回实体集合
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <returns></returns>
        public Task<List<T>> QueryAsync<T>() where T : class
        {
            return Task.FromResult(Query<T>());
        }
        /// <summary>
        /// 异步返回单个字段值
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <returns></returns>
        public Task<T> GetSingleAsync<T>()
        {
            return Task.FromResult(GetSingle<T>());
        }
        /// <summary>
        /// 异步返回单行实体
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <returns></returns>
        public Task<T> GetSingleRowAsync<T>() where T : class
        {
            return Task.FromResult(GetSingleRow<T>());
        }

        /// <summary>
        /// 异步通过条件查询表的记录数
        /// </summary>
        /// <typeparam name="T">实体类型</typeparam>
        /// <param name="condition">条件</param>
        /// <returns></returns>
        public Task<long> CountAsync<TClass>(Expression<Func<TClass, bool>> where) where TClass : class, new()
        {
            return Task.FromResult(Count(where));
        }
#endif
        /// <summary>
        /// 返回sql脚本实体
        /// </summary>
        /// <returns></returns>
        public virtual List<SqlEntity> GetSqls() { return sqlList; }

        /// <summary>
        /// 返回sql脚本实体
        /// </summary>
        /// <returns></returns>
        public virtual SqlEntity GetSql(bool isMerge = false) { return sqlent; }
        #endregion

        #region Execute
        /// <summary>
        /// 执行脚本
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public virtual bool Execute()
        {
            Build();
            if (sqlent.Sql.IsNotNullOrEmpty() && !sqlList.Contains(sqlent))
                sqlList.Add(sqlent);
            if (sqlList.Count > 0)
            {
                var ret = db.Execute(sqlList);
                Dispose();
                return ret;
            }
            else
                return false;
        }
#if !NET40
        /// <summary>
        /// 异步执行脚本
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public virtual Task<bool> ExecuteAsync()
        {
            return Task.FromResult(Execute());
        }
#endif
        /// <summary>
        /// 执行脚本,等同于Execute()
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public bool Exec { get { return Execute(); } }
        #endregion

        #region Count/Exists
        /// <summary>
        /// 通过主键，判断实体是否存在表中
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <returns></returns>
        protected virtual bool Exists<TClass>(TClass entity, Tuple<DbTableInfo, string, string> tableInfo = null) where TClass : class, new()
        {
            return Count(entity) > 0;
        }
        /// <summary>
        /// 通过条件表达式，判断实体是否存在表中
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <returns></returns>
        protected virtual bool Exists<TClass>(Expression<Func<TClass, bool>> where, Tuple<DbTableInfo, string, string> tableInfo = null) where TClass : class, new()
        {
            return Count(where) > 0;
        }

        /// <summary>
        /// 通过主键，判断实体是否存在表中
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <returns></returns>
        protected virtual long Count<TClass>(TClass entity, Tuple<DbTableInfo, string, string> tableInfo = null) where TClass : class, new()
        {
            if (tableInfo == null)
                tableInfo = GetTableInfo<TClass>();
            var tb = tableInfo.Item1;
            if (tb == null) return 0;

            var whereStr = BuildEntityWhere(sqlent, entity, null, tb);
            if (whereStr.IsNullOrEmpty())
            {
                whereStr = "(1=1)";
            }
            sqlent.Sql = whereStr;

            var count = Count<TClass>(sqlent, tableInfo);
            return count;
        }
        /// <summary>
        /// 通过条件表达式，判断实体是否存在表中
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <returns></returns>
        protected virtual long Count<TClass>(Expression<Func<TClass, bool>> where, Tuple<DbTableInfo, string, string> tableInfo = null) where TClass : class, new()
        {
            var whereStr = BuildWhereExp(sqlent, where, null);
            if (whereStr.IsNullOrEmpty())
            {
                whereStr = "(1=1)";
            }
            sqlent.Sql = whereStr;

            var count = Count<TClass>(sqlent);
            return count;
        }
        /// <summary>
        /// 
        /// </summary>
        /// <typeparam name="TClass"></typeparam>
        /// <param name="where"></param>
        /// <param name="tableInfo"></param>
        /// <returns></returns>
        protected virtual long Count<TClass>(SqlEntity where, Tuple<DbTableInfo, string, string> tableInfo = null) where TClass : class, new()
        {
            if (tableInfo == null)
                tableInfo = GetTableInfo<TClass>();
            var tb = tableInfo.Item1;
            if (tb == null) return 0;

            string sqlMeta = db.SqlDbProvider.GetCountSql(tableInfo.Item3);
            var whereStr = where.Sql;
            if (whereStr.IsNullOrEmpty())
            {
                whereStr = "(1=1)";
            }
            where.Sql = string.Format(sqlMeta, whereStr);

            return db.GetSingle<long>(where);
        }
        #endregion

        #region WithNextTableSequence
        /// <summary>
        /// 下个序列脚本
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="postfix"></param>
        /// <returns></returns>
        protected virtual string WithNextTableSequence<TClass>(string postfix = "_seq") where TClass : class, new()
        {
            var tb = Funs.GetDbTableInfo<TClass>(db);
            return db.SqlDbProvider.WithNextSequence(tb.TableName + postfix);
        }
        #endregion
        //-------------------------------------------------------------------------------------------------------------------------------
        #region CreateSqlEntity
        /// <summary>
        /// 创建执行脚本实体
        /// </summary>
        /// <returns></returns>
        protected virtual SqlEntity CreateSqlEntity()
        {
            return new SqlEntity(db);
        }
        #endregion
        #region AddDbParameter
        /// <summary>
        /// 添加参数
        /// </summary>
        /// <param name="colName">字段名称</param>
        /// <param name="val">参数值</param>
        /// <param name="sqlent"></param>
        /// <param name="sqlstr"></param>
        /// <param name="strand"></param>
        /// <param name="oper"></param>
        /// <param name="paramSuffix">参数前缀</param>
        protected virtual void AddDbParameter(ref StringBuilder sqlstr, string colName, object val, SqlEntity sqlent, string strand = "AND", string oper = "=", string paramSuffix = "wp_", DbTableInfo tb = null)
        {
            if (AddDbParameter(colName, val, sqlent, out string paraName, paramSuffix, tb))
            {
                addExpress(ref sqlstr, colName, paraName, strand, oper);
            }
            else if (paramSuffix.StartsWith("setp_") && (colName.ToLower() == "upatedate" || colName.ToLower() == "upatetime"))
            {
                addExpress(ref sqlstr, colName, Fun_GetDate, strand, oper);
            }
        }
        protected virtual void addExpress(ref StringBuilder express, string colName, string paraName, string strand = "AND", string oper = "=")
        {
            if (express.Length == 0)
                express.AppendFormat(" " + suffixLeft + "{0}" + suffixRigh + oper + "{1}", colName, paraName);
            else
                express.AppendFormat(" " + strand + "  " + suffixLeft + "{0}" + suffixRigh + oper + "{1}", colName, paraName);
        }

        /// <summary>
        /// 添加参数
        /// </summary>
        /// <param name="propName">参数名称</param>
        /// <param name="val">参数值</param>
        /// <param name="sqlent"></param>
        /// <param name="sqlstr"></param>
        /// <param name="strand"></param>
        /// <param name="oper"></param>
        /// <param name="paramSuffix">参数前缀</param>
        protected virtual bool AddDbParameter(string propName, object val, SqlEntity sqlent, out string parameterName, string paramSuffix = "wp_", DbTableInfo tb = null)
        {
            return ExpressionHelper.AddDbParameter(propName, val, sqlent, out parameterName, paramSuffix, tb);
        }
        #endregion
        //-------------------------------------------------------------------------------------------------------------------------------
        #region BuildWhere
        /// <summary>
        /// 通过实体值生成WHERE表达式
        /// </summary>
        /// <param name="_obj"></param>
        /// <param name="_WherePros">条件字段;如果为空，则通过表主键生成条件</param>
        /// <returns></returns>
        protected virtual string BuildEntityWhere<TClass>(SqlEntity sqle, TClass _obj, List<string> _WherePros = null, DbTableInfo tb = null) where TClass : class, new()
        {
            if (_obj == null) return "";
            if (tb == null)
            {
                var tableInfo = GetTableInfo<TClass>();
                tb = tableInfo.Item1;
            }
            if (sqle == null) return "";
            if (tb == null) return "";

            if (_WherePros == null || _WherePros.Count == 0)//空时，通过主键生成条件
            {
                _WherePros = new List<string>();
                _WherePros = tb.GetKeyColumns()?.Select(s => s.Name)?.ToList();
            }

            //where条件字符串
            var wheres = new StringBuilder();
            _WherePros = _WherePros?.Distinct().ToList();
            if (_WherePros.IsNullOrEmpty_())
            {
                wheres.AppendFormat("{0}={1}", 1, 2);
                return "";
            }
            else
            {
                _WherePros.ForEach(propName =>
                {
                    var prop = tb.GetColumn(propName);
                    if (prop != null && prop.PropertyInfo != null)
                    {
                        AddDbParameter(ref wheres, prop.Name, prop.PropertyInfo.FastGetValue(_obj), sqle, "AND", "=", "wp_", tb);
                    }
                });
            }

            return wheres.ToString();
        }

        /// <summary>
        /// 添加条件表达式
        /// </summary>
        /// <typeparam name="TClass"></typeparam>
        /// <param name="where"></param>
        /// <returns></returns>
        protected virtual bool BuildWhere<TClass>(SqlEntity sqle, Expression<Func<TClass, bool>> where, bool? isAnd = null, string tableAlias = "") where TClass : class, new()
        {
            if (fromStr.IsNullOrEmpty())
                From<TClass>();

            var whereStr = BuildWhereExp(sqle, where, isAnd, tableAlias);
            if (whereStr.IsNotNullOrEmpty())
            {
                WhereList.Add(whereStr);
            }
            return true;
        }
        /// <summary>
        /// 根据参数指定的条件式生成Where条件SQLwhere也进行条件值的参数绑定。
        /// </summary>
        /// <param name="parameters">数据参数对象</param>
        /// <param name="where">条件式</param>
        /// <typeparam name="TClass">映射到表格的类型</typeparam>
        /// <returns>生成的Where句（没有条件指定时为空字符）</returns>
        /// <remarks>如果在parameters中指定了null，则不进行参数绑定，生成静态SQL。</remarks>
        protected virtual string BuildWhereExp<TClass>(SqlEntity sqle, Expression<Func<TClass, bool>> where, bool? isAnd = null, string tableAlias = "") where TClass : class, new()
        {
            return ExpressionHelper.BuildWhere(sqle, where, isAnd, tableAlias);
        }
        protected virtual string BuildJoinOn<TClassA, TClassB>(JoinType joinType, SqlEntity sqle, Expression<Func<TClassA, TClassB, bool>> JoinWhere, string tableAliasB = "") where TClassA : class, new() where TClassB : class, new()
        {
            if (JoinWhere == null)
            {
                return string.Empty;
            }
            var JoinTemplate = " {0} JOIN {1} {2} ON {3} ";
            var tableInfo = GetTableInfo<TClassB>(tableAliasB);
            var whereStr = ExpressionHelper.BuildWhere(sqlent, JoinWhere.Body);

            return string.Format(
                  JoinTemplate,
                  joinType.ToString(),
                  tableInfo.Item1.TableName,
                  tableInfo.Item2.Replace(".", ""),
                  whereStr);
        }
        #endregion

        #region BuildInsert
        /// <summary>
        /// 通过实体构建执行脚本
        /// </summary>
        /// <typeparam name="TClass"></typeparam>
        /// <param name="_obj"></param>
        /// <param name="isMerge"></param>
        /// <returns></returns>
        protected virtual SqlEntity BuildInsert<TClass>(TClass _obj, Tuple<DbTableInfo, string, string> tableInfo = null, bool isMerge = false) where TClass : class, new()
        {
            if (tableInfo == null)
                tableInfo = GetTableInfo<TClass>();
            var tb = tableInfo.Item1;
            if (tb == null) return null;

            var sqle = CreateSqlEntity();
            //通过实体设置字段
            var dicfields = BuildFields(_obj, (f) => f.PropertyInfo != null && f.Type.IsNotNullOrEmpty() && f.IsIdentity == false, tableInfo);
            if (dicfields.IsNullOrEmpty_()) return null;
            //生成脚本
            if (BuildInsertSQL(sqle, dicfields, tableInfo, isMerge))
                return sqle;

            return null;
        }

        /// <summary>
        /// 通过表达式构建执行脚本
        /// </summary>
        /// <typeparam name="TClass"></typeparam>
        /// <param name="values"></param>
        /// <returns></returns>
        protected virtual SqlEntity BuildInsert<TClass>(Expression<Func<TClass, TClass>> values, Tuple<DbTableInfo, string, string> tableInfo = null, bool isMerge = false) where TClass : class, new()
        {
            if (tableInfo == null)
                tableInfo = GetTableInfo<TClass>();
            var tb = tableInfo.Item1;
            if (tb == null) return null;

            var sqle = CreateSqlEntity();
            //通过实体设置字段
            var dicfields = BuildFields(values, (f) => f.PropertyInfo == null || f.Type.IsNullOrEmpty() || f.IsIdentity == true);
            if(dicfields.IsNullOrEmpty_()) return null;
            //生成脚本
            if (BuildInsertSQL(sqle, dicfields, tableInfo, isMerge))
                return sqle;

            return null;
        }
        /// <summary>
        /// 生成插入脚本
        /// </summary>
        /// <param name="sqle"></param>
        /// <param name="dicfields"></param>
        /// <param name="tableInfo"></param>
        /// <param name="isMerge"></param>
        /// <returns></returns>
        protected virtual bool BuildInsertSQL(SqlEntity sqle, Dictionary<string, Tuple<string, object, Type>> dicfields, Tuple<DbTableInfo, string, string> tableInfo = null, bool isMerge = false)
        {
            if (sqle == null || tableInfo == null) return false;
            var tb = tableInfo.Item1;
            if (tb == null) return false;

            var paramSuffix = "valp_";
            var sqlMeta = db.SqlDbProvider.GetInsertSql(tableInfo.Item3, isMerge);
            var sqlfield = "";
            var sqlval = "";

            dicfields.ForEach(dic =>
            {
                var val = dic.Value.Item2;
                if (!val.IsNullOrDBNull_() && AddDbParameter(dic.Key, dic.Value.Item2, sqle, out string parameterName, paramSuffix, tableInfo?.Item1))
                {
                    sqlfield += "," + dic.Value.Item1;
                    sqlval += "," + parameterName;
                }
                else if (val.IsNullOrDBNull_() && dic.Value.Item3 == typeof(DateTime)
                   && (dic.Key.ToLower() == "createdate" || dic.Key.ToLower() == "updatedate"
                       || dic.Key.ToLower() == "createtime" || dic.Key.ToLower() == "updatetime"))
                {
                    sqlfield += "," + dic.Value.Item1;
                    sqlval += "," + Fun_GetDate;
                }
            });
            sqlfield = sqlfield.Trim(',');
            sqlval = sqlval.Trim(',');
            sqle.Sql = string.Format(sqlMeta, sqlfield, sqlval);

            return true;
        }
        #endregion

        #region BuildUpdate
        /// <summary>
        /// 通过实体设置更新字段并生成脚本
        /// </summary>
        /// <typeparam name="TClass"></typeparam>
        /// <param name="_obj"></param>
        /// <param name="wheresStr"></param>
        /// <param name="isForSet"></param>
        /// <param name="fields"></param>
        /// <param name="tableInfo"></param>
        /// <param name="isMerge"></param>
        /// <returns></returns>
        protected virtual SqlEntity BuildUpdate<TClass>(TClass _obj, bool isForSet = true, List<string> fields = null, SqlEntity whereSqle = null, Tuple<DbTableInfo, string, string> tableInfo = null, bool isMerge = false) where TClass : class, new()
        {
            if (tableInfo == null)
                tableInfo = GetTableInfo<TClass>();
            var tb = tableInfo.Item1;
            if (tb == null) return null;

            var sqle = CreateSqlEntity();
            //生成条件表达式
            var whereStr = "";
            if (whereSqle == null || whereSqle.Sql.IsNullOrEmpty())
            {
                whereStr = BuildEntityWhere(sqle, _obj, null, tb);
                if (whereStr.IsNullOrEmpty())
                {
                    return null;
                }
            }
            else
            {
                sqle.AddParameters(whereSqle.Parameters?.ToArray());
                whereStr = whereSqle.Sql;
            }
            //通过实体设置更新字段
            var dicfields = BuildFields(_obj
                , (f) => f.IsIdentity == false && f.IsPrimaryKey == false && f.PropertyInfo != null && f.Type.IsNotNullOrEmpty()
                                    && (fields == null || fields.Count == 0 || ((isForSet && fields.Exists(p => p.Trim().ToLower() == f.Name.Trim().ToLower()))
                                                                            || (!isForSet && !fields.Exists(p => p.Trim().ToLower() == f.Name.Trim().ToLower())))
                                    )
                , tableInfo);
            //生成脚本
            if (BuildUpdateSQL(sqle, dicfields, whereStr, tableInfo, isMerge))
                return sqle;
            return null;
        }
        /// <summary>
        /// 生成更新脚本
        /// </summary>
        /// <param name="sqle"></param>
        /// <param name="dicfields"></param>
        /// <param name="wheresStr"></param>
        /// <param name="tableInfo"></param>
        /// <param name="isMerge"></param>
        /// <returns></returns>
        protected virtual bool BuildUpdateSQL(SqlEntity sqle, Dictionary<string, Tuple<string, object, Type>> dicfields, string wheresStr, Tuple<DbTableInfo, string, string> tableInfo = null, bool isMerge = false)
        {
            if (sqle == null || tableInfo == null) return false;
            var tb = tableInfo.Item1;
            if (tb == null) return false;

            var fromStr = tableInfo.Item3;
            var sqlMeta = db.SqlDbProvider.GetUpdateSql(tableInfo.Item3, isMerge);

            var sets = new StringBuilder();
            dicfields?.ForEach(dic =>
            {
                if (AddDbParameter(dic.Key, dic.Value.Item2, sqle, out string paraName, "setp_", tupleTableInfo?.Item1))
                {
                    if (sets.Length > 0) sets.Append(",");
                    sets.AppendFormat(" {0}={1}", dic.Value.Item1, paraName);
                }
            });
            if (sets.ToString().IsNullOrEmpty()) return false;
            if (isMerge)
                sqle.Sql = string.Format(sqlMeta, sets.ToString());
            else
                sqle.Sql = string.Format(sqlMeta, sets.ToString(), wheresStr);
            return true;
        }
        #endregion

        #region BuildFields
        /// <summary>
        /// 通过实体生成字段
        /// </summary>
        /// <typeparam name="TClass"></typeparam>
        /// <param name="values"></param>
        /// <returns></returns>
        protected virtual Dictionary<string, Tuple<string, object, Type>> BuildFields<TClass>(TClass _obj
            , Func<DbColumnInfo, bool> columnFiller, Tuple<DbTableInfo, string, string> tableInfo = null, string tableAlias = "") where TClass : class, new()
        {
            var dicfields = new Dictionary<string, Tuple<string, object, Type>>();
            if (_obj == null)
            {
                return dicfields;
            }
            if (tableInfo == null)
                tableInfo = GetTableInfo<TClass>();
            var tb = tableInfo.Item1;
            if (tb == null) return dicfields;

            var columns = tb.TableInfo.FindAll(f => columnFiller?.Invoke(f) == true);
            if (columns == null || columns.Count == 0) return null;
            columns.ForEach(column =>
            {
                var colname = Funs.FormatFieldName(tableAlias, column.Name, suffixLeft, suffixRigh);
                dicfields[column.Name] = Tuple.Create(colname, column.PropertyInfo.FastGetValue(_obj)
                                                    , column.PropertyInfo?.PropertyType.GetUnderType());
            });

            return dicfields;
        }

        /// <summary>
        /// 通过表达式生成字段
        /// </summary>
        /// <typeparam name="TClass"></typeparam>
        /// <param name="values"></param>
        /// <returns></returns>
        protected virtual Dictionary<string, Tuple<string, object, Type>> BuildFields<TClass1, TClass2>(Expression<Func<TClass2, TClass1>> values
            , Func<DbColumnInfo, bool> columnFiller, string tableAlias = "") where TClass1 : class, new() //where TClass2 : class, new()
        {
            var dicfields = new Dictionary<string, Tuple<string, object, Type>>();
            if (values == null)
            {
                throw new ArgumentException("未指定目标列");
                //return dicfields;
            }
            var initExpr = (values.Body as MemberInitExpression);
            if (initExpr == null || initExpr.Bindings.Count == 0)
            {//return dicfields; //
                throw new ArgumentException("未指定目标列");
            }
            var tableInfo1 = GetTableInfo<TClass1>();
            var tb1 = tableInfo1.Item1;
            if (tb1 == null) return dicfields;

            foreach (var member in initExpr.Bindings)
            {
                var column = tb1.GetColumn(member.Member.Name);
                if (columnFiller?.Invoke(column) == true) //|| column.IsIdentity == true || column.IsPrimaryKey == true
                    continue;

                var valtype = column?.PropertyInfo?.PropertyType.GetUnderType();
                // 用固有的SQL条件式记述用方法指定值的情况下，调用SQL条件式生成方法来组装SQL
                object valueSql = ExpressionHelper.TryBindFieldExpr(db.SqlDbProvider, column, (member as MemberAssignment).Expression, tb1, tableAlias);
                if (valueSql == null)
                {   // 否则取出更新值绑定
                    valueSql = ExpressionHelper.EvaluateValue((member as MemberAssignment).Expression);
                }
                else
                {
                    valtype = typeof(FieldExpr);
                }
                var colname = Funs.FormatFieldName(tableAlias, column.Name, suffixLeft, suffixRigh);
                dicfields[column.Name] = Tuple.Create(colname, valueSql, valtype);
            }

            return dicfields;
        }

        protected virtual Dictionary<string, Tuple<string, object, Type>> BuildFields<TClass>(Expression<Func<TClass, dynamic>> values
           , Func<DbColumnInfo, bool> columnFiller, string tableAlias = "") where TClass : class, new()
        {
            var dicfields = new Dictionary<string, Tuple<string, object, Type>>();
            if (values == null)
            {
                throw new ArgumentException("未指定目标列");
                //return dicfields;
            }
            var tableInfo1 = GetTableInfo<TClass>();
            var tb1 = tableInfo1.Item1;
            if (tb1 == null) return dicfields;

            if (values.Body is NewExpression ne)
            {
                var members = ne.Members;
                if (members.Count > 0)
                {
                    for (var i = 0; i < members.Count; i++)
                    {
                        var mbname = members[i].GetColumnName();
                        var column = tb1.GetColumn(mbname);
                        if (columnFiller?.Invoke(column) == true)
                            continue;

                        var valtype = column?.PropertyInfo?.PropertyType.GetUnderType();
                        // 用固有的SQL条件式记述用方法指定值的情况下，调用SQL条件式生成方法来组装SQL
                        var expr = ne.Arguments[i];
                        object valueSql = ExpressionHelper.TryBindFieldExpr(db.SqlDbProvider, column, expr, tb1, tableAlias);
                        if (valueSql == null)
                        {   // 否则取出更新值绑定
                            valueSql = ExpressionHelper.EvaluateValue(expr);
                        }
                        else
                        {
                            valtype = typeof(FieldExpr);
                        }
                        var colname = Funs.FormatFieldName(tableAlias, mbname, suffixLeft, suffixRigh);
                        dicfields[mbname] = Tuple.Create(colname, valueSql, valtype);
                    }
                }
            }
            else if (values.Body is MemberInitExpression initExpr)
            {
                //var initExpr = (values.Body as MemberInitExpression);
                //if (initExpr == null || initExpr.Bindings.Count == 0)
                //{
                //    //return dicfields; //
                //    throw new ArgumentException("未指定目标列");
                //}
                foreach (var member in initExpr.Bindings)
                {
                    var column = tb1.GetColumn(member.Member.GetColumnName());
                    if (columnFiller?.Invoke(column) == true) //|| column.IsIdentity == true || column.IsPrimaryKey == true
                        continue;

                    var valtype = column?.PropertyInfo?.PropertyType.GetUnderType();
                    // 用固有的SQL条件式记述用方法指定值的情况下，调用SQL条件式生成方法来组装SQL
                    object valueSql = ExpressionHelper.TryBindFieldExpr(db.SqlDbProvider, column, (member as MemberAssignment).Expression, tb1, tableAlias);
                    if (valueSql == null)
                    {   // 否则取出更新值绑定
                        valueSql = ExpressionHelper.EvaluateValue((member as MemberAssignment).Expression);
                    }
                    else
                    {
                        valtype = typeof(FieldExpr);
                    }
                    var colname = Funs.FormatFieldName(tableAlias, column.Name, suffixLeft, suffixRigh);
                    dicfields[column.Name] = Tuple.Create(colname, valueSql, valtype);
                }
            }
            else
            {
                throw new ArgumentException("未指定目标列");
            }
            return dicfields;
        }
        #endregion

        #region UpdateOrInsert
        /// <summary>
        /// 通用保存数据方法（自动新增或更新动作）
        /// </summary>
        /// <typeparam name="TClass"></typeparam>
        /// <param name="entities"></param>
        /// <param name="whereSqle"></param>
        /// <param name="isForSet"></param>
        /// <param name="columns"></param>
        /// <returns></returns>
        protected bool UpdateOrInsertBase<TClass>(List<TClass> entities, SqlEntity whereSqle, bool isForSet, IEnumerable<string> columns
            , Tuple<DbTableInfo, string, string> tableInfo = null) where TClass : class, new()
        {
            if (!entities.IsNullOrEmpty_())
            {
                if (tableInfo == null)
                    tableInfo = GetTableInfo<TClass>();
                sqlList.Clear();

                var whereStr = whereSqle?.Sql;//?.ReplaceIgnoreCase("WHERE", "").TrimPrefixName("AND").TrimPrefixName("OR");
                entities.ForEach(entity =>
                {
                    SqlEntity sqle = null;
                    if (whereSqle == null || whereSqle.Sql.IsNullOrEmpty())
                    {
                        if (whereSqle == null) whereSqle = CreateSqlEntity();

                        whereStr = BuildEntityWhere(whereSqle, entity, null, tableInfo?.Item1);
                        if (whereStr.IsNullOrEmpty())
                        {
                            return;
                        }
                        whereStr = whereSqle?.Sql?.ReplaceIgnoreCase("WHERE", "").TrimPrefixName("AND").TrimPrefixName("OR");
                    }
                    //----------------------------------------------------------------------------------------------------------
                    if (db.DBType == DatabaseType.MySql || db.DBType == DatabaseType.PostgreSql || db.DBType == DatabaseType.MsAccess)
                    {
                        if (Count(entity) < 1)
                        {
                            sqle = BuildInsert(entity, tableInfo, false);
                        }
                        else
                        {
                            sqle = BuildUpdate(entity, isForSet, columns?.CastToList<string>(), whereSqle, tableInfo, false);
                        }
                    }
                    else
                    {
                        var sqlMeta = db.SqlDbProvider.GetMergeSql(tableInfo.Item3);
                        sqle = BuildInsert(entity, tableInfo, true);
                        var insertsql = sqle.Sql; sqle.Sql = "";
                        whereSqle.AddParameters(sqle.Parameters?.ToArray());

                        sqle = BuildUpdate(entity, isForSet, columns?.CastToList<string>(), whereSqle, tableInfo, true);
                        if (sqle != null)
                        {
                            var updtesql = sqle.Sql;

                            if (db.DBType == DatabaseType.Sqlite)
                                sqle.Sql = insertsql.Replace("INSERT", $"REPLACE INTO {tableInfo.Item3}");
                            else
                                sqle.Sql = string.Format(sqlMeta, whereStr, updtesql, insertsql);
                        }
                    }
                    if (sqle != null)
                    {
                        sqlList.Add(sqle);
                    }
                    whereSqle?.Parameters.Clear();
                    whereSqle.Sql = "";
                });
                return Exec;
            }
            return false;
        }
        /// <summary>
        /// 通用保存数据方法（自动新增或更新动作）
        /// </summary>
        /// <typeparam name="TClass"></typeparam>
        /// <param name="entities"></param>
        /// <param name="whereSqle"></param>
        /// <param name="isForSet"></param>
        /// <param name="columns"></param>
        /// <returns></returns>
        protected bool UpdateOrInsertBase<TClass>(Expression<Func<TClass, TClass>> fields, Expression<Func<TClass, bool>> where
            , Tuple<DbTableInfo, string, string> tableInfo = null) where TClass : class, new()
        {
            if (fields != null)
            {
                if (tableInfo == null)
                    tableInfo = GetTableInfo<TClass>();
                sqlList.Clear();
                var whereStr = BuildWhereExp(sqlent, where, null);
                if (whereStr.IsNullOrEmpty())
                {
                    return false;
                }
                //whereStr = whereStr.ReplaceIgnoreCase("WHERE", "").TrimPrefixName("AND").TrimPrefixName("OR");

                SqlEntity sqle = null;

                var dicSetfields = BuildFields(fields, (column) => column.IsNullOrEmpty() || column.IsIdentity == true || column.IsPrimaryKey == true);
                //----------------------------------------------------------------------------------------------------------
                if (db.DBType == DatabaseType.MySql)
                {
                    if (Count(where) < 1)
                    {
                        sqle = BuildInsert(fields, tableInfo, false);
                    }
                    else
                    {
                        sqle = CreateSqlEntity();
                        sqle.AddParameters(sqlent.Parameters?.ToArray());
                        BuildUpdateSQL(sqle, dicSetfields, whereStr, tableInfo, false);
                    }
                }
                else
                {
                    var sqlMeta = db.SqlDbProvider.GetMergeSql(tableInfo.Item3);
                    sqle = BuildInsert(fields, tableInfo, true);
                    if (sqle != null)
                    {
                        var insertsql = sqle.Sql; sqle.Sql = "";

                        sqle.AddParameters(sqlent.Parameters?.ToArray());
                        BuildUpdateSQL(sqle, dicSetfields, whereStr, tableInfo, true);
                        var updtesql = sqle.Sql;

                        if (db.DBType == DatabaseType.Sqlite)
                            sqle.Sql = insertsql.Replace("INSERT", $"REPLACE INTO {tableInfo.Item3}");
                        else
                            sqle.Sql = string.Format(sqlMeta, whereStr, updtesql, insertsql);
                    }
                }
                if (sqle != null)
                {
                    sqlList.Add(sqle);
                }

                return Exec;
            }
            return false;
        }
        #endregion
    }
}
